﻿using Infragistics.WebUI.UltraWebGrid;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.ReportBusiness;
using uMES.LeanManufacturing.DBUtility;
using System.Collections;

public partial class Custom_BaseManage_ImportRoleEmployee : BaseForm
{
    int m_PageSize = 6;
    uMESRoleMenuBusiness RoleMenuBusiness = new uMESRoleMenuBusiness ();
    uMESCommonBusiness common = new uMESCommonBusiness();
    protected void Page_Load(object sender, EventArgs e)
    {
        uMESMasterPage master = this.Master as uMESMasterPage;
        master.strNavigation = "当前位置：导入人员角色";
        master.strTitle = "导入人员角色";

        DisplayMessage("",true);
        if (!IsPostBack)
        {
           

        }
    }




    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try {
            ResultModel result = new ResultModel(false, "");

            var strPath = bffSelectPath.FileName;

            if (strPath == string.Empty)
            {
                DisplayMessage("请选择Excel文件", false);
                return;
            }

            SaveFileToServer(ref strPath);

            DataTable dt = new DataTable();

            result = ImportData(strPath, ref dt);

            DisplayMessage(result.Message,result.IsSuccess);

        } catch (Exception ex) {
            DisplayMessage(ex.Message,false);
        }
       


        
    }


    /// <summary>
    /// 保存到服务器
    /// </summary>
    /// <param name="strpath"></param>
    /// <returns></returns>
    bool SaveFileToServer(ref string strpath)
    {

        bool result = false;

        var fileName = strpath.Substring(strpath.LastIndexOf(@"\") + 1);

        File.Delete(strpath);

        strpath = @"C:\订单导入备份\" + fileName;
        bffSelectPath.SaveAs(strpath);

        bffSelectPath.Dispose();
        result = true;
        return result;
    }

    /// <summary>
    /// 去除空白行
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    DataTable RemoveBlankRow(DataTable dt)
    {
        DataTable dt2 = dt.Clone();

        foreach (DataRow dr in dt.Rows)
        {
            bool isImport = false;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!dr.IsNull(i))
                {
                    isImport = true;
                    break;
                }
            }
            if (isImport)
                dt2.ImportRow(dr);
        }

        return dt2;
    }


    /// <summary>
    /// 导入数据
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="dt"></param>
    /// <param name="apiUsername"></param>
    /// <param name="apiPassword"></param>
    /// <returns></returns>
    public ResultModel ImportData(string filePath, ref DataTable dt)
    {
        ResultModel re = new ResultModel();
        re.IsSuccess = false;

        string type = filePath.Substring(filePath.LastIndexOf(".") + 1);

        string strconn = "";

        if (type.ToLower() == "xls")
        {
            strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
        }
        else if (type.ToLower() == "xlsx")
        {
            strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
        }
        else
        {
            re.Message = "请选择Excel类型文件";
            return re;
        }

        //DataTable dt = new DataTable();
        using (var odbcConnection = new System.Data.OleDb.OleDbConnection(strconn))
        {
            try
            {
                odbcConnection.Open();

                var strsql = "select * from [" + "Sheet1" + "$]";
                var odbcDataAdapater = new System.Data.OleDb.OleDbDataAdapter(strsql, odbcConnection);
                odbcDataAdapater.Fill(dt);
                odbcConnection.Close();

                File.Delete(filePath);
            }
            catch (Exception ex)
            {
                File.Delete(filePath);

                re.Message = ex.Message;
                return re;
            }

        }

        dt = RemoveBlankRow(dt);

        re = TransferDtCol(dt);
        if (re.IsSuccess == false)
            return re;

        re.IsSuccess = false;
        dt.Columns.Add("Message");

        string[] judeCondition = { "EmployeeName", "RoleName" };

        if (!judgeNullData(dt, judeCondition))//为空判断
        {
            re.Message = "请填入必填项";
            return re;
        }

        re = SaveData(dt);


        if (re.IsSuccess)
            re.Message = "导入成功";
        else
            return re;


        return re;
    }


    /// <summary>
    /// 列名转换
    /// </summary>
    /// <param name="dt"></param>
    ResultModel TransferDtCol(DataTable dt)
    {

        ResultModel re = new ResultModel();
        re.IsSuccess = false;

        int num = 0;

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            num++;
            if (dt.Columns[i].ColumnName == "人员")
            {
                dt.Columns[i].ColumnName = "EmployeeName";
            }
            else if (dt.Columns[i].ColumnName == "角色")
            {
                dt.Columns[i].ColumnName = "RoleName";
            }
            else
            {
                num--;
            }

        }
        
        re.IsSuccess = true;
        return re;
    }

    /// <summary>
    /// 为空判断
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="judeCondition"></param>
    /// <returns></returns>
    bool judgeNullData(DataTable dt, string[] judeCondition)
    {

        string sqlCondition = "";
        foreach (string str in judeCondition)
        {
            sqlCondition += str + " is null or ";
        }
        sqlCondition = sqlCondition.Remove(sqlCondition.LastIndexOf("or"));

        var selectRows = dt.Select(sqlCondition);

        if (selectRows.Length > 0)
        {
            foreach (DataRow dr in selectRows)
            {
                dr["Message"] = "请填入必填项";
            }
            return false;
        }

        return true;

    }


    ResultModel SaveData(DataTable dt)
    {
        ResultModel re = new ResultModel(false,"");

        var sqls = "SELECT ww.employeeid FROM  employee ww WHERE ww.employeename=" + "'{0}'";
        var sqls2 = "SELECT ww.roleid FROM  roledef ww WHERE ww.rolename=" + "'{0}'";
        var isExsit = "select 1 from roleemployee re where re.employeeid='{0}' and re.roleid='{1}'";
        string insertSql = "insert into roleemployee (createdate,createempid,employeeid,id,roleid,rolename) values (sysdate,'"+UserInfo["EmployeeID"] +"','{0}',sys_guid(),'{1}','{2}')";
        ArrayList insertSqls = new ArrayList();
        foreach (DataRow dr in dt.Rows) {

            var emplyeeID = OracleHelper.GetSingle(string.Format(sqls, dr["employeename"].ToString()));
            var roleID = OracleHelper.GetSingle(string.Format(sqls2, dr["rolename"].ToString()));

            if (emplyeeID == null || roleID == null) {
                re.Message = dr["employeename"].ToString() +","+ dr["rolename"].ToString() + ",人员或角色不存在";
                return re;
            }

            if (OracleHelper.GetSingle(string.Format(isExsit, emplyeeID.ToString(), roleID.ToString()))!=null) {
                continue;
            }

            insertSqls.Add(string.Format(insertSql, emplyeeID, roleID, dr["rolename"].ToString()));

        }

        OracleHelper.ExecuteSqlTran(insertSqls);

        re.IsSuccess = true;
        return re;
    }
}